/*
 *  Copyright (c) 2005-2009, WSO2 Inc. (http://www.wso2.org) All Rights Reserved.
 *
 *  WSO2 Inc. licenses this file to you under the Apache License,
 *  Version 2.0 (the "License"); you may not use this file except
 *  in compliance with the License.
 *  You may obtain a copy of the License at
 *
 *  http://www.apache.org/licenses/LICENSE-2.0
 *
 *  Unless required by applicable law or agreed to in writing,
 *  software distributed under the License is distributed on an
 *  "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
 *  KIND, either express or implied.  See the License for the
 *  specific language governing permissions and limitations
 *  under the License.
 *
 */
package org.wso2.carbon.registry.core.jdbc.dao;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.wso2.carbon.registry.core.*;
import org.wso2.carbon.registry.core.dao.ResourceDAO;
import org.wso2.carbon.registry.core.dao.TagsDAO;
import org.wso2.carbon.registry.core.dataaccess.DAOManager;
import org.wso2.carbon.registry.core.exceptions.RegistryException;
import org.wso2.carbon.registry.core.jdbc.DatabaseConstants;
import org.wso2.carbon.registry.core.jdbc.dataaccess.JDBCDatabaseTransaction;
import org.wso2.carbon.registry.core.jdbc.dataobjects.TaggingDO;
import org.wso2.carbon.registry.core.session.CurrentSession;
import org.wso2.carbon.registry.core.utils.RegistryUtils;
import org.wso2.carbon.utils.DBUtils;

import java.sql.*;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;

/**
 * An implementation of the {@link TagsDAO} to store tags on a JDBC-based database.
 */
public class JDBCTagsDAO implements TagsDAO {

    private static final Log log = LogFactory.getLog(JDBCTagsDAO.class);
    private ResourceDAO resourceDAO;
    protected static final Object ADD_TAG_LOCK = new Object();

    /**
     * Default constructor
     *
     * @param daoManager instance of the data access object manager.
     */
    public JDBCTagsDAO(DAOManager daoManager) {
        this.resourceDAO = daoManager.getResourceDAO();
    }

    public void addTagging(String tagName, ResourceImpl resource, String userID)
            throws RegistryException {

        JDBCDatabaseTransaction.ManagedRegistryConnection conn =
                JDBCDatabaseTransaction.getConnection();
        PreparedStatement ps1 = null;
        PreparedStatement ps2 = null;
        PreparedStatement ps3 = null;
        ResultSet result = null;
        try {
            String sql1 =
                    "INSERT INTO REG_TAG (REG_TAG_NAME, REG_USER_ID, REG_TAGGED_TIME, " +
                            "REG_TENANT_ID) VALUES (?,?,?,?)";
            String sql2 = "SELECT MAX(REG_ID) FROM REG_TAG";
            long now = System.currentTimeMillis();

            String dbProductName = conn.getMetaData().getDatabaseProductName();
            boolean returnsGeneratedKeys = DBUtils.canReturnGeneratedKeys(dbProductName);
            if (returnsGeneratedKeys) {
                ps1 = conn.prepareStatement(sql1, new String[]{DBUtils
                        .getConvertedAutoGeneratedColumnName(dbProductName,
                                DatabaseConstants.ID_FIELD)});
            } else {
                ps1 = conn.prepareStatement(sql1);
            }
            ps1.setString(1, tagName);
            ps1.setString(2, userID);
            ps1.setDate(3, new Date(now));
            ps1.setInt(4, CurrentSession.getTenantId());
            if (returnsGeneratedKeys) {
                ps1.executeUpdate();
                result = ps1.getGeneratedKeys();
            } else {
                synchronized (ADD_TAG_LOCK) {
                    ps1.executeUpdate();
                    ps2 = conn.prepareStatement(sql2);
                    result = ps2.executeQuery();
                }
            }
            if (result.next()) {
                int tagId = result.getInt(1);

                String sql3 = "INSERT INTO REG_RESOURCE_TAG (REG_TAG_ID, REG_PATH_ID, " +
                        "REG_RESOURCE_NAME, REG_TENANT_ID) " +
                        "VALUES(?,?,?,?)";
                ps3 = conn.prepareStatement(sql3);

                ps3.setInt(1, tagId);
                ps3.setInt(2, resource.getPathID());
                ps3.setString(3, resource.getName());
                ps3.setInt(4, CurrentSession.getTenantId());

                ps3.executeUpdate();
            }

        } catch (SQLException e) {

            String msg = "Failed to add tag " + tagName + " to resource " + resource.getPath() +
                    " by user " + userID + ". " + e.getMessage();
            log.error(msg, e);
            throw new RegistryException(msg, e);
        } finally {
            try {
                try {
                    if (result != null) {
                        result.close();
                    }
                } finally {
                    try {
                        if (ps1 != null) {
                            ps1.close();
                        }
                    } finally {
                        try {
                            if (ps2 != null) {
                                ps2.close();
                            }
                        } finally {
                            if (ps3 != null) {
                                ps3.close();
                            }
                        }
                    }
                }
            } catch (SQLException ex) {
                String msg = RegistryConstants.RESULT_SET_PREPARED_STATEMENT_CLOSE_ERROR;
                log.error(msg, ex);
            }
        }
    }

    public void addTaggings(ResourceImpl resource, TaggingDO[] taggingDOs)
            throws RegistryException {
        JDBCDatabaseTransaction.ManagedRegistryConnection conn =
                JDBCDatabaseTransaction.getConnection();
        long now = System.currentTimeMillis();

        for (TaggingDO taggingDO : taggingDOs) {

            PreparedStatement ps = null;
            PreparedStatement ps2 = null;
            ResultSet result = null;
            try {
                String sql =
                        "INSERT INTO REG_TAG (REG_TAG_NAME, REG_USER_ID, REG_TAGGED_TIME, " +
                                "REG_TENANT_ID) VALUES (?,?,?,?)";

                String dbProductName = conn.getMetaData().getDatabaseProductName();
                ps = conn.prepareStatement(sql, new String[]{
                        DBUtils.getConvertedAutoGeneratedColumnName(dbProductName, "REG_ID")});
                ps.setString(1, taggingDO.getTagName());
                ps.setString(2, taggingDO.getTaggedUserName());
                ps.setDate(3, new Date(now));
                ps.setInt(4, CurrentSession.getTenantId());

                ps.executeUpdate();

                result = ps.getGeneratedKeys();
                if (result.next()) {
                    int tagId = result.getInt(1);

                    String sql2 = "INSERT INTO REG_RESOURCE_TAG (REG_TAG_ID, REG_PATH_ID, " +
                            "REG_RESOURCE_NAME, REG_TENANT_ID) " +
                            "VALUES(?,?,?,?)";
                    ps2 = conn.prepareStatement(sql2);

                    ps2.setInt(1, tagId);
                    ps2.setInt(2, resource.getPathID());
                    ps2.setString(3, resource.getName());
                    ps2.setInt(4, CurrentSession.getTenantId());

                    ps2.executeUpdate();
                }
            } catch (SQLException e) {
                String msg =
                        "Failed to add tags to resource " + resource.getPath() + ". " +
                                e.getMessage();
                log.error(msg, e);
                throw new RegistryException(msg, e);
            } finally {
                // closing open prepared statements & result sets before moving on to next iteration
                try {
                    try {
                        if (result != null) {
                            result.close();
                        }
                    } finally {
                        try {
                            if (ps != null) {
                                ps.close();
                            }
                        } finally {
                            if (ps2 != null) {
                                ps2.close();
                            }
                        }
                    }
                } catch (SQLException ex) {
                    String msg = RegistryConstants.RESULT_SET_PREPARED_STATEMENT_CLOSE_ERROR;
                    log.error(msg, ex);
                }
            }
        }
    }

    public void copyTags(ResourceImpl fromResource, ResourceImpl toResource)
            throws RegistryException {

        if (fromResource == null || toResource == null || fromResource.getPath() == null
                || fromResource.getPath().equals(toResource.getPath())) {
            // no special copying needed
            return;
        }
        List<TaggingDO> tagList = getTagDOs(fromResource);

        addTaggings(toResource, tagList.toArray(new TaggingDO[tagList.size()]));
    }

    public boolean taggingExists(String tagName, ResourceImpl resourceImpl, String userID)
            throws RegistryException {

        JDBCDatabaseTransaction.ManagedRegistryConnection conn =
                JDBCDatabaseTransaction.getConnection();

        ResultSet result = null;
        PreparedStatement ps = null;

        try {
            if (resourceImpl instanceof CollectionImpl) {
                String sql =
                        "SELECT T.REG_ID FROM REG_TAG T, REG_RESOURCE_TAG RT WHERE " +
                                "LOWER(T.REG_TAG_NAME)=? AND T.REG_USER_ID =? AND " +
                                "T.REG_ID=RT.REG_TAG_ID AND RT.REG_PATH_ID=? AND " +
                                "RT.REG_RESOURCE_NAME IS NULL AND T.REG_TENANT_ID=? " +
                                "AND RT.REG_TENANT_ID=?";
                ps = conn.prepareStatement(sql);
                if (tagName == null) {
                    ps.setString(1, null);
                } else {
                    ps.setString(1, tagName.toLowerCase());
                }
                ps.setString(2, userID);
                ps.setInt(3, resourceImpl.getPathID());
                ps.setInt(4, CurrentSession.getTenantId());
                ps.setInt(5, CurrentSession.getTenantId());
            } else {
                String sql =
                        "SELECT T.REG_ID FROM REG_TAG T, REG_RESOURCE_TAG RT WHERE " +
                                "LOWER(T.REG_TAG_NAME)=? AND T.REG_USER_ID =? AND " +
                                "T.REG_ID=RT.REG_TAG_ID AND RT.REG_PATH_ID=? AND " +
                                "RT.REG_RESOURCE_NAME=? AND T.REG_TENANT_ID=? " +
                                "AND RT.REG_TENANT_ID=?";
                ps = conn.prepareStatement(sql);
                if (tagName == null) {
                    ps.setString(1, null);
                } else {
                    ps.setString(1, tagName.toLowerCase());
                }
                ps.setString(2, userID);
                ps.setInt(3, resourceImpl.getPathID());
                ps.setString(4, resourceImpl.getName());
                ps.setInt(5, CurrentSession.getTenantId());
                ps.setInt(6, CurrentSession.getTenantId());
            }
            result = ps.executeQuery();

            boolean tagExists = false;
            if (result.next()) {
                tagExists = true;
            }

            return tagExists;

        } catch (SQLException e) {

            String msg = "Failed to check the existence of the tag " + tagName +
                    " on resource " + resourceImpl.getPath() + " by user " + userID + ". " +
                    e.getMessage();
            log.error(msg, e);
            throw new RegistryException(msg, e);
        } finally {
            try {
                try {
                    if (result != null) {
                        result.close();
                    }
                } finally {
                    if (ps != null) {
                        ps.close();
                    }
                }
            } catch (SQLException ex) {
                String msg = RegistryConstants.RESULT_SET_PREPARED_STATEMENT_CLOSE_ERROR;
                log.error(msg, ex);
            }
        }

    }

    public void removeTags(ResourceImpl resource, String tag)
            throws RegistryException {

        removeTags(resource, tag, "*");
    }

    public void removeTags(ResourceImpl resource, String tag, String userID)
            throws RegistryException {
        JDBCDatabaseTransaction.ManagedRegistryConnection conn =
                JDBCDatabaseTransaction.getConnection();

        TaggingDO[] taggingDOs = getTagging(resource, tag, userID);
        if (taggingDOs == null || taggingDOs.length == 0) {
            return;
        }
        for (TaggingDO taggingDO : taggingDOs) {
            PreparedStatement ps = null;
            PreparedStatement ps2 = null;
            try {
                String sql = "DELETE FROM REG_RESOURCE_TAG WHERE REG_TAG_ID= ? AND REG_TENANT_ID=?";
                ps2 = conn.prepareStatement(sql);
                ps2.setLong(1, taggingDO.getTagID());
                ps2.setInt(2, CurrentSession.getTenantId());
                ps2.executeUpdate();

                sql = "DELETE FROM REG_TAG WHERE REG_ID= ? AND REG_TENANT_ID=?";
                ps = conn.prepareStatement(sql);
                ps.setLong(1, taggingDO.getTagID());
                ps.setInt(2, CurrentSession.getTenantId());
                ps.executeUpdate();
            } catch (SQLException e) {
                String msg = "Failed to remove tag " + tag + " from the resource " +
                        resource.getPath() + "with user id. " + userID + e.getMessage();
                log.error(msg, e);
                throw new RegistryException(msg, e);
            } finally {
                // closing prepared statements before moving on to next iteration
                try {
                    try {
                        if (ps != null) {
                            ps.close();
                        }
                    } finally {
                        if (ps2 != null) {
                            ps2.close();
                        }
                    }
                } catch (SQLException ex) {
                    String msg = RegistryConstants.RESULT_SET_PREPARED_STATEMENT_CLOSE_ERROR;
                    log.error(msg, ex);
                }
            }
        }
    }

    public void removeTags(ResourceImpl resource)
            throws RegistryException {
        JDBCDatabaseTransaction.ManagedRegistryConnection conn =
                JDBCDatabaseTransaction.getConnection();

        PreparedStatement ps1 = null, ps2 = null;

        TaggingDO[] taggingDOs = getTagging(resource);
        if (taggingDOs == null) {
            return;
        }
        try {

            String sql = "DELETE FROM REG_RESOURCE_TAG WHERE REG_TAG_ID= ? AND REG_TENANT_ID=?";
            ps1 = conn.prepareStatement(sql);

            sql = "DELETE FROM REG_TAG WHERE REG_ID= ? AND REG_TENANT_ID=?";
            ps2 = conn.prepareStatement(sql);

            for (TaggingDO taggingDO : taggingDOs) {
                ps1.setLong(1, taggingDO.getTagID());
                ps1.setInt(2, CurrentSession.getTenantId());
                ps2.setLong(1, taggingDO.getTagID());
                ps2.setInt(2, CurrentSession.getTenantId());
                ps1.addBatch();
                ps2.addBatch();
            }

            if (taggingDOs.length > 0) {
                try {
                    ps1.executeBatch();
                    ps2.executeBatch();
                } catch (SQLException e) {
                    ps1.clearBatch();
                    ps2.clearBatch();
                    // the exception will be handled in the next catch block
                    throw e;
                }
            }

        } catch (SQLException e) {

            String msg =
                    "Failed to remove tagging from " + resource.getPath() + ". " + e.getMessage();
            log.error(msg, e);
            throw new RegistryException(msg, e);
        } finally {
            try {
                try {
                    if (ps1 != null) {
                        ps1.close();
                    }
                } finally {
                    if (ps2 != null) {
                        ps2.close();
                    }
                }
            } catch (SQLException ex) {
                String msg = RegistryConstants.RESULT_SET_PREPARED_STATEMENT_CLOSE_ERROR;
                log.error(msg, ex);
            }
        }
    }
    
    
    public void removeVersionTags(long regVersion)
            throws RegistryException {
        JDBCDatabaseTransaction.ManagedRegistryConnection conn =
                JDBCDatabaseTransaction.getConnection();

        PreparedStatement ps1 = null, ps2 = null;

        List<Long> taggingIds = getTagIds(regVersion);
        
        if (taggingIds == null) {
            return;
        }
        
        try {

            String sql = "DELETE FROM REG_RESOURCE_TAG WHERE REG_TAG_ID= ? AND REG_TENANT_ID=?";
            ps1 = conn.prepareStatement(sql);

            sql = "DELETE FROM REG_TAG WHERE REG_ID= ? AND REG_TENANT_ID=?";
            ps2 = conn.prepareStatement(sql);

            for (long l : taggingIds) {
                ps1.setLong(1, l);
                ps1.setInt(2, CurrentSession.getTenantId());
                ps2.setLong(1, l);
                ps2.setInt(2, CurrentSession.getTenantId());
                ps1.addBatch();
                ps2.addBatch();
            }

            if (taggingIds.size() > 0) {
                try {
                    ps1.executeBatch();
                    ps2.executeBatch();
                } catch (SQLException e) {
                    ps1.clearBatch();
                    ps2.clearBatch();
                    // the exception will be handled in the next catch block
                    throw e;
                }
            }

        } catch (SQLException e) {

            String msg =
                    "Failed to remove tagging for the version: " + regVersion + ". " + e.getMessage();
            log.error(msg, e);
            throw new RegistryException(msg, e);
        } finally {
            try {
                try {
                    if (ps1 != null) {
                        ps1.close();
                    }
                } finally {
                    if (ps2 != null) {
                        ps2.close();
                    }
                }
            } catch (SQLException ex) {
                String msg = RegistryConstants.RESULT_SET_PREPARED_STATEMENT_CLOSE_ERROR;
                log.error(msg, ex);
            }
        }
    }

    /**
     * Method to get resource paths containing the given tag.
     *
     * @param tag  the tag name.
     * @param conn the connection to use.
     *
     * @return array of resource paths.
     * @throws RegistryException if an error occurs while getting the resource path.
     */
    @SuppressWarnings("unused")
    public String[] getPathsWithTag(String tag, Connection conn) throws RegistryException {
        ResultSet results = null;
        PreparedStatement s = null;
        List<String> pathList = new ArrayList<String>();
        try {
            String sql = "SELECT  RT.REG_PATH_ID, RT.REG_RESOURCE_NAME, RT.REG_VERSION FROM " +
                    "REG_RESOURCE_TAG RT, REG_TAG T WHERE T.REG_TAG_NAME = ? AND " +
                    "RT.REG_TAG_ID=T.REG_ID AND RT.REG_TENANT_ID=? AND T.REG_TENANT_ID=?";

            s = conn.prepareStatement(sql);
            s.setString(1, tag);
            s.setInt(2, CurrentSession.getTenantId());
            s.setInt(3, CurrentSession.getTenantId());

            results = s.executeQuery();

            while (results.next()) {
                long version = results.getLong(DatabaseConstants.VERSION_FIELD);
                if (version > 0) {
                    String path = resourceDAO.getPath(version);
                    if (path != null) {
                        pathList.add(path);
                    }
                } else {
                    int pathId = results.getInt(DatabaseConstants.PATH_ID_FIELD);
                    String resourceName = results.getString(DatabaseConstants.RESOURCE_NAME_FIELD);
                    String path = resourceDAO.getPath(pathId, resourceName, true);
                    if (path != null) {
                        pathList.add(path);
                    }
                }
            }
        } catch (SQLException e) {

            String msg = "Failed to get paths associated with the given tag " +
                    tag + ". " + e.getMessage();
            log.error(msg, e);
            throw new RegistryException(msg, e);
        } finally {
            try {
                try {
                    if (results != null) {
                        results.close();
                    }
                } finally {
                    if (s != null) {
                        s.close();
                    }
                }
            } catch (SQLException ex) {
                String msg = RegistryConstants.RESULT_SET_PREPARED_STATEMENT_CLOSE_ERROR;
                log.error(msg, ex);
            }
        }
        return pathList.toArray(new String[pathList.size()]);
    }

    public String[] getTags(ResourceImpl resourceImpl) throws RegistryException {

        JDBCDatabaseTransaction.ManagedRegistryConnection conn =
                JDBCDatabaseTransaction.getConnection();
        ResultSet results = null;
        PreparedStatement ps = null;
        try {

            if (resourceImpl instanceof CollectionImpl) {
                String sql =
                        "SELECT T.REG_TAG_NAME FROM REG_TAG T, REG_RESOURCE_TAG RT WHERE " +
                                "T.REG_ID=RT.REG_TAG_ID AND RT.REG_PATH_ID=? AND " +
                                "RT.REG_RESOURCE_NAME IS NULL AND T.REG_TENANT_ID=? " +
                                "AND RT.REG_TENANT_ID=?";
                ps = conn.prepareStatement(sql);
                ps.setInt(1, resourceImpl.getPathID());
                ps.setInt(2, CurrentSession.getTenantId());
                ps.setInt(3, CurrentSession.getTenantId());
            } else {
                String sql =
                        "SELECT T.REG_TAG_NAME FROM REG_TAG T, REG_RESOURCE_TAG RT WHERE " +
                                "T.REG_ID=RT.REG_TAG_ID AND RT.REG_PATH_ID=? AND " +
                                "RT.REG_RESOURCE_NAME=? AND T.REG_TENANT_ID=? " +
                                "AND RT.REG_TENANT_ID=?";
                ps = conn.prepareStatement(sql);
                ps.setInt(1, resourceImpl.getPathID());
                ps.setString(2, resourceImpl.getName());
                ps.setInt(3, CurrentSession.getTenantId());
                ps.setInt(4, CurrentSession.getTenantId());
            }
            results = ps.executeQuery();

            List<String> tagList = new ArrayList<String>();
            while (results.next()) {
                tagList.add(results.getString(DatabaseConstants.TAG_NAME_FIELD));
            }

            return tagList.toArray(new String[tagList.size()]);

        } catch (SQLException e) {

            String msg = "Failed to get tags associated with the resource path " +
                    resourceImpl.getPath() + ". " + e.getMessage();
            log.error(msg, e);
            throw new RegistryException(msg, e);
        } finally {
            try {
                try {
                    if (results != null) {
                        results.close();
                    }
                } finally {
                    if (ps != null) {
                        ps.close();
                    }
                }
            } catch (SQLException ex) {
                String msg = RegistryConstants.RESULT_SET_PREPARED_STATEMENT_CLOSE_ERROR;
                log.error(msg, ex);
            }
        }
    }

    public List<TaggingDO> getTagDOs(ResourceImpl resourceImpl) throws RegistryException {

        JDBCDatabaseTransaction.ManagedRegistryConnection conn =
                JDBCDatabaseTransaction.getConnection();
        ResultSet results = null;
        PreparedStatement ps = null;
        List<TaggingDO> tagList = new ArrayList<TaggingDO>();
        try {

            if (resourceImpl instanceof CollectionImpl) {
                String sql =
                        "SELECT T.REG_TAG_NAME, T.REG_USER_ID, T.REG_TAGGED_TIME " +
                                "FROM REG_TAG T, REG_RESOURCE_TAG RT WHERE " +
                                "T.REG_ID=RT.REG_TAG_ID AND RT.REG_PATH_ID=? AND " +
                                "RT.REG_RESOURCE_NAME IS NULL AND T.REG_TENANT_ID=? " +
                                "AND RT.REG_TENANT_ID=?";
                ps = conn.prepareStatement(sql);
                ps.setInt(1, resourceImpl.getPathID());
                ps.setInt(2, CurrentSession.getTenantId());
                ps.setInt(3, CurrentSession.getTenantId());
            } else {
                String sql =
                        "SELECT T.REG_TAG_NAME, T.REG_USER_ID, T.REG_TAGGED_TIME " +
                                "FROM REG_TAG T, REG_RESOURCE_TAG RT WHERE " +
                                "T.REG_ID=RT.REG_TAG_ID AND RT.REG_PATH_ID=? AND " +
                                "RT.REG_RESOURCE_NAME=? AND T.REG_TENANT_ID=? " +
                                "AND RT.REG_TENANT_ID=?";
                ps = conn.prepareStatement(sql);
                ps.setInt(1, resourceImpl.getPathID());
                ps.setString(2, resourceImpl.getName());
                ps.setInt(3, CurrentSession.getTenantId());
                ps.setInt(4, CurrentSession.getTenantId());
            }
            results = ps.executeQuery();

            while (results.next()) {
                TaggingDO taggingDO = new TaggingDO();
                taggingDO.setTagName(results.getString(1));
                taggingDO.setTaggedUserName(results.getString(2));
                taggingDO.setTaggedTime(results.getDate(3));
                tagList.add(taggingDO);
            }

            return tagList;

        } catch (SQLException e) {

            String msg = "Failed to get tags associated with the resource path " +
                    resourceImpl.getPath() + ". " + e.getMessage();
            log.error(msg, e);
            throw new RegistryException(msg, e);
        } finally {
            try {
                try {
                    if (results != null) {
                        results.close();
                    }
                } finally {
                    if (ps != null) {
                        ps.close();
                    }
                }
            } catch (SQLException ex) {
                String msg = RegistryConstants.RESULT_SET_PREPARED_STATEMENT_CLOSE_ERROR;
                log.error(msg, ex);
            }
        }
    }

    public List getPathsWithAnyTag(String[] tags) throws RegistryException {

        if (tags == null || tags.length == 0) {
            return null;
        }

        JDBCDatabaseTransaction.ManagedRegistryConnection conn =
                JDBCDatabaseTransaction.getConnection();

        StringBuffer sqlBuf = new StringBuffer();
        sqlBuf.append("SELECT DISTINCT RT.REG_PATH_ID, RT.REG_RESOURCE_NAME FROM " +
                "REG_RESOURCE_TAG RT, REG_TAG T WHERE RT.REG_TAG_ID=T.REG_ID ");
        sqlBuf.append(" AND (");
        for (int i = 0; i < tags.length; i++) {
            if (i > 0) {
                sqlBuf.append(" OR ");
            }
            sqlBuf.append("lower(T.REG_TAG_NAME)=?");
        }
        sqlBuf.append(") AND RT.REG_TENANT_ID=? AND T.REG_TENANT_ID=?");

        List<String> resourcePaths = new ArrayList<String>();

        ResultSet results = null;
        PreparedStatement s = null;
        try {
            s = conn.prepareStatement(sqlBuf.toString());

            int i;
            for (i = 0; i < tags.length; i++) {
                if (tags[i] == null) {
                    s.setString(i + 1, tags[i]);
                } else {
                    s.setString(i + 1, tags[i].toLowerCase());
                }
            }
            s.setInt(i + 1, CurrentSession.getTenantId());
            s.setInt(i + 2, CurrentSession.getTenantId());

            results = s.executeQuery();
            while (results.next()) {
                int pathId = results.getInt(DatabaseConstants.PATH_ID_FIELD);
                String resourceName = results.getString(DatabaseConstants.RESOURCE_NAME_FIELD);
                String path = resourceDAO.getPath(pathId, resourceName, true);
                if (path != null) {
                    resourcePaths.add(path);
                }
            }

        } catch (SQLException e) {

            String msg = "Failed to resource paths with any of the tags " +
                    Arrays.toString(tags) + ". " + e.getMessage();
            log.error(msg, e);
            throw new RegistryException(msg, e);
        } finally {
            try {
                try {
                    if (results != null) {
                        results.close();
                    }
                } finally {
                    if (s != null) {
                        s.close();
                    }
                }
            } catch (SQLException ex) {
                String msg = RegistryConstants.RESULT_SET_PREPARED_STATEMENT_CLOSE_ERROR;
                log.error(msg, ex);
            }
        }

        return resourcePaths;
    }

    public long getTagCount(ResourceImpl resourceImpl, String tag) throws RegistryException {

        JDBCDatabaseTransaction.ManagedRegistryConnection conn =
                JDBCDatabaseTransaction.getConnection();

        ResultSet result = null;
        PreparedStatement ps = null;
        try {
            if (resourceImpl instanceof CollectionImpl) {
                String sql =
                        "SELECT COUNT(T.REG_TAG_NAME) " +
                                "FROM REG_TAG T, REG_RESOURCE_TAG RT WHERE " +
                                "lower(T.REG_TAG_NAME)=? AND " +
                                "T.REG_ID=RT.REG_TAG_ID AND RT.REG_PATH_ID=? AND " +
                                "RT.REG_RESOURCE_NAME IS NULL AND T.REG_TENANT_ID = ? " +
                                "AND RT.REG_TENANT_ID=? " +
                                "GROUP BY RT.REG_PATH_ID";
                ps = conn.prepareStatement(sql);
                if (tag == null) {
                    ps.setString(1, null);
                } else {
                    ps.setString(1, tag.toLowerCase());
                }
                ps.setInt(2, resourceImpl.getPathID());
                ps.setInt(3, CurrentSession.getTenantId());
                ps.setInt(4, CurrentSession.getTenantId());
            } else {
                String sql =
                        "SELECT COUNT(T.REG_TAG_NAME) " +
                                "FROM REG_TAG T, REG_RESOURCE_TAG RT WHERE " +
                                "lower(T.REG_TAG_NAME)=? AND " +
                                "T.REG_ID=RT.REG_TAG_ID AND RT.REG_PATH_ID=? " +
                                "AND RT.REG_RESOURCE_NAME=? " +
                                "AND T.REG_TENANT_ID=? AND RT.REG_TENANT_ID=? " +
                                "GROUP BY RT.REG_PATH_ID, RT.REG_RESOURCE_NAME";
                ps = conn.prepareStatement(sql);
                if (tag == null) {
                    ps.setString(1, null);
                } else {
                    ps.setString(1, tag.toLowerCase());
                }
                ps.setInt(2, resourceImpl.getPathID());
                ps.setString(3, resourceImpl.getName());
                ps.setInt(4, CurrentSession.getTenantId());
                ps.setInt(5, CurrentSession.getTenantId());
            }
            result = ps.executeQuery();

            long tagCount = 0;
            if (result.next()) {
                tagCount = result.getLong(1);
            }

            return tagCount;

        } catch (SQLException e) {

            String msg = "Failed to get tag count of tag " + tag +
                    " on resource " + resourceImpl.getPath() + ". " + e.getMessage();
            log.error(msg, e);
            throw new RegistryException(msg, e);
        } finally {
            try {
                try {
                    if (result != null) {
                        result.close();
                    }
                } finally {
                    if (ps != null) {
                        ps.close();
                    }
                }
            } catch (SQLException ex) {
                String msg = RegistryConstants.RESULT_SET_PREPARED_STATEMENT_CLOSE_ERROR;
                log.error(msg, ex);
            }
        }
    }

    public Tag[] getTagsWithCount(ResourceImpl resourceImpl) throws RegistryException {

        JDBCDatabaseTransaction.ManagedRegistryConnection conn =
                JDBCDatabaseTransaction.getConnection();

        List<Tag> tagList = new ArrayList<Tag>();
        ResultSet result = null;
        PreparedStatement ps = null;
        try {
            if (resourceImpl instanceof CollectionImpl) {
                String sql =
                        "SELECT T.REG_TAG_NAME, COUNT(T.REG_ID) FROM REG_TAG T, " +
                                "REG_RESOURCE_TAG RT " +
                                "WHERE RT.REG_PATH_ID=? AND RT.REG_RESOURCE_NAME IS NULL AND " +
                                "T.REG_TENANT_ID=? AND RT.REG_TENANT_ID=? AND " +
                                "T.REG_ID=RT.REG_TAG_ID GROUP BY T.REG_TAG_NAME";

                ps = conn.prepareStatement(sql);
                ps.setInt(1, resourceImpl.getPathID());
                ps.setInt(2, CurrentSession.getTenantId());
                ps.setInt(3, CurrentSession.getTenantId());
            } else {
                String sql =
                        "SELECT T.REG_TAG_NAME, COUNT(T.REG_ID) FROM REG_TAG T, " +
                                "REG_RESOURCE_TAG RT " +
                                "WHERE RT.REG_PATH_ID=? AND RT.REG_RESOURCE_NAME=? AND " +
                                "T.REG_TENANT_ID=? AND RT.REG_TENANT_ID=? AND " +
                                "T.REG_ID=RT.REG_TAG_ID GROUP BY T.REG_TAG_NAME";

                ps = conn.prepareStatement(sql);
                ps.setInt(1, resourceImpl.getPathID());
                ps.setString(2, resourceImpl.getName());
                ps.setInt(3, CurrentSession.getTenantId());
                ps.setInt(4, CurrentSession.getTenantId());
            }
            result = ps.executeQuery();
            while (result.next()) {
                Tag tag = new Tag();
                tag.setTagName(result.getString(DatabaseConstants.TAG_NAME_FIELD));
                tag.setTagCount(result.getLong(2));
                tagList.add(tag);
            }

        } catch (SQLException e) {

            String msg = "Failed to get tags and tag counts of the resource " +
                    resourceImpl.getPath() + ". " + e.getMessage();
            log.error(msg, e);
            throw new RegistryException(msg, e);
        } finally {
            try {
                try {
                    if (result != null) {
                        result.close();
                    }
                } finally {
                    if (ps != null) {
                        ps.close();
                    }
                }
            } catch (SQLException ex) {
                String msg = RegistryConstants.RESULT_SET_PREPARED_STATEMENT_CLOSE_ERROR;
                log.error(msg, ex);
            }
        }

        return tagList.toArray(new Tag[tagList.size()]);
    }

    public TaggingDO[] getTagging(ResourceImpl resource, String tag, String userID)
            throws RegistryException {

        JDBCDatabaseTransaction.ManagedRegistryConnection conn =
                JDBCDatabaseTransaction.getConnection();

        ResultSet result = null;
        PreparedStatement ps = null;

        List<TaggingDO> taggingDOs = new ArrayList<TaggingDO>();
        try {
            TaggingDO taggingDO;

            if (resource instanceof CollectionImpl) {
                String sql =
                        "SELECT T.REG_ID, T.REG_TAGGED_TIME FROM REG_TAG T, REG_RESOURCE_TAG RT " +
                                " WHERE RT.REG_PATH_ID = ? AND RT.REG_RESOURCE_NAME IS NULL " +
                                "AND RT.REG_TAG_ID=T.REG_ID AND T.REG_TAG_NAME=? ";
                if (!userID.equals("*")) {
                    sql = sql + "AND T.REG_USER_ID=? ";
                }
                sql = sql + " AND T.REG_TENANT_ID=? AND RT.REG_TENANT_ID=? ";
                ps = conn.prepareStatement(sql);
                ps.setInt(1, resource.getPathID());
                ps.setString(2, tag);
                int nextParam = 3;
                if (!userID.equals("*")) {
                    ps.setString(nextParam, userID);
                    nextParam++;
                }
                ps.setInt(nextParam, CurrentSession.getTenantId());
                nextParam++;
                ps.setInt(nextParam, CurrentSession.getTenantId());
            } else {
                String sql =
                        "SELECT T.REG_ID, T.REG_TAGGED_TIME FROM REG_TAG T, REG_RESOURCE_TAG RT " +
                                " WHERE RT.REG_PATH_ID = ? AND RT.REG_RESOURCE_NAME = ? " +
                                "AND RT.REG_TAG_ID=T.REG_ID AND T.REG_TAG_NAME=? ";
                if (!userID.equals("*")) {
                    sql = sql + "AND T.REG_USER_ID=?";
                }
                sql = sql + " AND T.REG_TENANT_ID=? AND RT.REG_TENANT_ID=?";

                ps = conn.prepareStatement(sql);
                ps.setInt(1, resource.getPathID());
                ps.setString(2, resource.getName());
                ps.setString(3, tag);
                int nextParam = 4;
                if (!userID.equals("*")) {
                    ps.setString(nextParam, userID);
                    nextParam++;
                }
                ps.setInt(nextParam, CurrentSession.getTenantId());
                nextParam++;
                ps.setInt(nextParam, CurrentSession.getTenantId());
            }

            result = ps.executeQuery();
            while (result.next()) {

                java.util.Date taggedTime = new java.util.Date(
                        result.getTimestamp(DatabaseConstants.TAGGED_TIME_FIELD).getTime());

                taggingDO = new TaggingDO();
                taggingDO.setResourcePath(resource.getPath());
                taggingDO.setTagName(tag);
                taggingDO.setTaggedTime(taggedTime);
                taggingDO.setTaggedUserName(userID);
                taggingDO.setTagID(result.getLong(DatabaseConstants.ID_FIELD));

                taggingDOs.add(taggingDO);
            }

            return taggingDOs.toArray(new TaggingDO[taggingDOs.size()]);

        } catch (SQLException e) {

            String msg = "Failed to get tagging information for tag " + tag +
                    " on resource " + resource.getPath() + " by user " + userID + ". " +
                    e.getMessage();
            log.error(msg, e);
            throw new RegistryException(msg, e);
        } finally {
            try {
                try {
                    if (result != null) {
                        result.close();
                    }
                } finally {
                    if (ps != null) {
                        ps.close();
                    }
                }
            } catch (SQLException ex) {
                String msg = RegistryConstants.RESULT_SET_PREPARED_STATEMENT_CLOSE_ERROR;
                log.error(msg, ex);
            }
        }
    }

    public TaggingDO[] getTagging(ResourceImpl resource)
            throws RegistryException {

        JDBCDatabaseTransaction.ManagedRegistryConnection conn =
                JDBCDatabaseTransaction.getConnection();

        List<TaggingDO> taggingDOs = new ArrayList<TaggingDO>();
        ResultSet results = null;
        PreparedStatement ps = null;
        try {
            TaggingDO taggingDO;

            if (resource instanceof CollectionImpl) {
                String sql = "SELECT T.REG_ID, T.REG_TAG_NAME, T.REG_USER_ID, " +
                        "T.REG_TAGGED_TIME FROM REG_TAG T, REG_RESOURCE_TAG RT " +
                        "WHERE RT.REG_PATH_ID = ? AND RT.REG_RESOURCE_NAME IS NULL AND " +
                        "RT.REG_TAG_ID=T.REG_ID AND T.REG_TENANT_ID=? AND RT.REG_TENANT_ID=?";
                ps = conn.prepareStatement(sql);
                ps.setInt(1, resource.getPathID());
                ps.setInt(2, CurrentSession.getTenantId());
                ps.setInt(3, CurrentSession.getTenantId());
            } else {
                String sql = "SELECT T.REG_ID, T.REG_TAG_NAME, T.REG_USER_ID, " +
                        "T.REG_TAGGED_TIME FROM REG_TAG T, REG_RESOURCE_TAG RT " +
                        "WHERE RT.REG_PATH_ID = ? AND RT.REG_RESOURCE_NAME = ? AND " +
                        "RT.REG_TAG_ID=T.REG_ID AND T.REG_TENANT_ID=? AND RT.REG_TENANT_ID=?";

                ps = conn.prepareStatement(sql);
                ps.setInt(1, resource.getPathID());
                ps.setString(2, resource.getName());
                ps.setInt(3, CurrentSession.getTenantId());
                ps.setInt(4, CurrentSession.getTenantId());
            }

            results = ps.executeQuery();
            while (results.next()) {

                java.util.Date taggedTime = new java.util.Date(
                        results.getTimestamp(DatabaseConstants.TAGGED_TIME_FIELD).getTime());

                taggingDO = new TaggingDO();
                taggingDO.setResourcePath(resource.getPath());
                taggingDO.setTagName(results.getString(DatabaseConstants.TAG_NAME_FIELD));
                taggingDO.setTaggedTime(taggedTime);
                taggingDO.setTaggedUserName(results.getString(DatabaseConstants.USER_ID_FIELD));
                taggingDO.setTagID(results.getLong(DatabaseConstants.ID_FIELD));
                taggingDOs.add(taggingDO);
            }


        } catch (SQLException e) {

            String msg = "Failed to get tagging information for the resource " +
                    resource.getPath() + ". " + e.getMessage();
            log.error(msg, e);
            throw new RegistryException(msg, e);
        } finally {
            try {
                try {
                    if (results != null) {
                        results.close();
                    }
                } finally {
                    if (ps != null) {
                        ps.close();
                    }
                }
            } catch (SQLException ex) {
                String msg = RegistryConstants.RESULT_SET_PREPARED_STATEMENT_CLOSE_ERROR;
                log.error(msg, ex);
            }
        }

        return taggingDOs.toArray(new TaggingDO[taggingDOs.size()]);
    }

    public TaggingDO getTagging(long taggingID)
            throws RegistryException {

        JDBCDatabaseTransaction.ManagedRegistryConnection conn =
                JDBCDatabaseTransaction.getConnection();

        ResultSet result = null;
        PreparedStatement s = null;
        try {
            String sql = "SELECT T.REG_USER_ID, T.REG_TAG_NAME, T.REG_TAGGED_TIME, " +
                    "RT.REG_PATH_ID, RT.REG_RESOURCE_NAME " +
                    "FROM REG_TAG T, REG_RESOURCE_TAG RT WHERE " +
                    "T.REG_ID=? AND T.REG_ID=RT.REG_TAG_ID AND T.REG_TENANT_ID=? " +
                    "AND RT.REG_TENANT_ID=?";

            s = conn.prepareStatement(sql);
            s.setLong(1, taggingID);
            s.setInt(2, CurrentSession.getTenantId());
            s.setInt(3, CurrentSession.getTenantId());

            TaggingDO taggingDO = null;
            result = s.executeQuery();
            if (result.next()) {

                java.util.Date taggedTime = new java.util.Date(
                        result.getTimestamp(DatabaseConstants.TAGGED_TIME_FIELD).getTime());

                taggingDO = new TaggingDO();
                // TODO
                //taggingDO.setResourceID(result.getString(DatabaseConstants.AID_FIELD));
                taggingDO.setTagName(result.getString(DatabaseConstants.TAG_NAME_FIELD));
                taggingDO.setTaggedUserName(result.getString(DatabaseConstants.USER_ID_FIELD));
                taggingDO.setTaggedTime(taggedTime);

                String resourcePath;
                int pathId = result.getInt(DatabaseConstants.PATH_ID_FIELD);
                String resourceName = result.getString(DatabaseConstants.RESOURCE_NAME_FIELD);
                resourcePath = resourceDAO.getPath(pathId, resourceName, true);
                if (resourcePath != null) {
                    taggingDO.setResourcePath(resourcePath);
                }
            }

            return taggingDO;

        } catch (SQLException e) {

            String msg = "Failed to get tagging information for tag ID " +
                    taggingID + ". " + e.getMessage();
            log.error(msg, e);
            throw new RegistryException(msg, e);
        } finally {
            try {
                try {
                    if (result != null) {
                        result.close();
                    }
                } finally {
                    if (s != null) {
                        s.close();
                    }
                }
            } catch (SQLException ex) {
                String msg = RegistryConstants.RESULT_SET_PREPARED_STATEMENT_CLOSE_ERROR;
                log.error(msg, ex);
            }
        }
    }    
    
    private List<Long> getTagIds(long regVersionId) throws RegistryException {

        JDBCDatabaseTransaction.ManagedRegistryConnection conn =
                JDBCDatabaseTransaction.getConnection();
        PreparedStatement ps = null;
        ResultSet results = null;
        List<Long> idList = new ArrayList<Long>();
    	
        try {     	
        		
        		String sql = "SELECT T.REG_ID FROM REG_TAG T, REG_RESOURCE_TAG RT WHERE " +
        			"RT.REG_VERSION = ? AND RT.REG_TENANT_ID = ? AND RT.REG_TENANT_ID=T.REG_TENANT_ID " +
        			"AND RT.REG_TAG_ID=T.REG_ID";
        	//"SELECT T.REG_ID FROM REG_TAG T, REG_RESOURCE_TAG RT WHERE " +
    		//	"RT.REG_PATH_ID = ? AND RT.REG_VERSION = ? AND RT.REG_TENANT_ID = ? AND RT.REG_RESOURCE_NAME IS NULL AND RT.REG_TENANT_ID=T.REG_TENANT_ID " +
    		//	"AND RT.REG_TAG_ID=T.REG_ID";
        		
        		ps = conn.prepareStatement(sql);        		
        		ps.setLong(1, regVersionId);        		
        		ps.setInt(2, CurrentSession.getTenantId());        	

        		results = ps.executeQuery();
        	
        	while(results.next()){
        		idList.add(results.getLong(1));       		
        	}       	
        } catch (Exception ex) {
            String msg = "Failed to retreive the Tags with the REG_VERSION: " +
                    regVersionId + ". " + ex.getMessage();                
            log.error(msg, ex);
            throw new RegistryException(msg, ex);
        } finally {
            try {
                try {
                    if (results != null) {
                        results.close();
                    }
                } finally {
                    if (ps != null) {
                        ps.close();
                    }
                }
            } catch (SQLException ex) {
                String msg = RegistryConstants.RESULT_SET_PREPARED_STATEMENT_CLOSE_ERROR;
                log.error(msg, ex);
            }
        }        
    	return idList;
    }

    public ResourceImpl getResourceWithMinimumData(String path) throws RegistryException {
        return RegistryUtils.getResourceWithMinimumData(path, resourceDAO, false);
    }

    public void moveTags(ResourceIDImpl source, ResourceIDImpl target) throws RegistryException {
        JDBCDatabaseTransaction.ManagedRegistryConnection conn =
                JDBCDatabaseTransaction.getConnection();
        PreparedStatement ps = null;
        try {
            if (source.isCollection()) {
                String sql = "UPDATE REG_RESOURCE_TAG SET REG_PATH_ID=? WHERE " +
                        "REG_PATH_ID=? AND REG_RESOURCE_NAME IS NULL AND REG_TENANT_ID=?";
                ps = conn.prepareStatement(sql);
                ps.setInt(1, target.getPathID());
                ps.setInt(2, source.getPathID());
                ps.setInt(3, CurrentSession.getTenantId());
                ps.executeUpdate();
            } else {
                String sql =
                        "UPDATE REG_RESOURCE_TAG SET REG_PATH_ID=?, REG_RESOURCE_NAME=? WHERE " +
                                "REG_PATH_ID=? AND REG_RESOURCE_NAME=? AND REG_TENANT_ID=?";
                ps = conn.prepareStatement(sql);
                ps.setInt(1, target.getPathID());
                ps.setString(2, target.getName());
                ps.setInt(3, source.getPathID());
                ps.setString(4, source.getName());
                ps.setInt(5, CurrentSession.getTenantId());
                ps.executeUpdate();
            }
        } catch (SQLException e) {
            String msg = "Failed to move tags from  " + source.getPath() +
                    " to " + target.getPath() + ". " + e.getMessage();
            log.error(msg, e);
            throw new RegistryException(msg, e);
        } finally {
            try {
                if (ps != null) {
                    ps.close();
                }
            } catch (SQLException ex) {
                String msg = RegistryConstants.RESULT_SET_PREPARED_STATEMENT_CLOSE_ERROR;
                log.error(msg, ex);
            }
        }
    }

    public void moveTagPaths(ResourceIDImpl source, ResourceIDImpl target)
            throws RegistryException {
        JDBCDatabaseTransaction.ManagedRegistryConnection conn =
                JDBCDatabaseTransaction.getConnection();
        PreparedStatement ps = null;
        try {
            String sql =
                    "UPDATE REG_RESOURCE_TAG SET REG_PATH_ID=? WHERE REG_PATH_ID=? " +
                            "AND REG_TENANT_ID=?";
            ps = conn.prepareStatement(sql);
            ps.setInt(1, target.getPathID());
            ps.setInt(2, source.getPathID());
            ps.setInt(3, CurrentSession.getTenantId());
            ps.executeUpdate();
        } catch (SQLException e) {
            String msg = "Failed to move tag paths from  " + source.getPath() +
                    " to " + target.getPath() + ". " + e.getMessage();
            log.error(msg, e);
            throw new RegistryException(msg, e);
        } finally {
            try {
                if (ps != null) {
                    ps.close();
                }
            } catch (SQLException ex) {
                String msg = RegistryConstants.RESULT_SET_PREPARED_STATEMENT_CLOSE_ERROR;
                log.error(msg, ex);
            }
        }
    }
}
